package pabudr_se;

import pabudr_se.*;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import pabudr_se.Atom;

/**
 *
 * @author paff
 */
public class dbConnection
{
    private final String sDbDrv = "org.apache.derby.jdbc.EmbeddedDriver";
    private final String sDbUrl = "jdbc:derby:Atom";
    private Connection cn = null;
    private Statement  st = null;
    private ResultSet  rs = null;
    private Properties props = new Properties();
    // ### define Atoms
    final static String[] ATOMS = {
        "('Wasserstoff', 31, 1.00794)",
        "('Helium', 28, 4.002602)",
        "('Lithium', 128, 6.941)",
        "('Beryllium', 96, 9.01218)",
        "('Bor', 82, 10.811)",
        "('Kohlenstoff', 77, 12.0107)",
        "('Sauerstoff', 66, 15.9994)",
        "('Fluor', 71, 18.9984)",
        "('Neon', 58, 20.1797)",
        "('Natrium', 154, 22.989770)",
        "('Magnesium', 141, 24.305)",
        "('Aluminium', 121, 26.981538)",
        "('Silicium', 111, 28.0855)",
        "('Phosphor', 106, 30.97376)",
        "('Schwefel', 102.5, 32.065)",
        "('Chlor', 102, 35.453)",
        "('Argon', 106, 39.948)",
        "('Kalium', 203, 39.0983)",
        "('Calcium', 176, 40.078)",
        "('Scandium', 170, 44.955910)",
        "('Titan', 160, 47.867)",
        "('Vanadium', 153, 50.9415)",
        "('Chrom', 139, 51.9961)",
        "('Mangan', 161, 54.938049)",
        "('Eisen', 152, 55.846)",
        "('Cobalt', 126, 58.93320)",
        "('Nickel', 121, 58.6934)",
        "('Kupfer', 138, 63.546)",
        "('Zink', 131, 65.409)",
        "('Gallium', 126, 69.723)",
        "('Germanium', 122, 72.61)",
        "('Arsen', 121, 74.92159)",
        "('Selen', 116, 78.96)",
        "('Brom', 114, 79.904)",
        "('Krypton', 110, 83.798)",
        "('Strontium', 195, 87.62)",
        "('Yttrium', 162, 88.90585)",
        "('Zirconium', 148, 91.224)",
        "('Niob', 137, 92.90638)",
        "('Molybdaen', 145, 95.94)",
        "('Technetium', 131, 98.9063)",
        "('Ruthenium', 126, 101.07)",
        "('Rhodium', 142, 102.90550)",
        "('Palladium', 131, 106.42)",
        "('Silber', 153, 107.8682)",
        "('Cadmium', 148, 112.411)",
        "('Indium', 144, 114.818)",
        "('Zinn', 141, 118.710)",
        "('Antimon', 138, 121.750)",
        "('Tellur', 135, 127.60)",
        "('Iod', 133, 126.90447)",
        "('Xenon', 130, 131.293)",
        "('Caesium', 244, 132.9054)",
        "('Barium', 215, 137.327)",
        "('Lanthan', 169, 138.9055)",
        "('Cer', 165, 140.116)",
        "('Praseodym', 165, 140.90765)",
        "('Neodym', 164, 144.24)",
        "('Promethium', 164, 146.9151)",
        "('Samarium', 166, 150.36)",
        "('Europium', 208, 151964)",
        "('Gadolinium', 180, 157.25)",
        "('Terbium', 176, 158.92534)",
        "('Dysprosium', 175.2, 162.50)",
        "('Holmium', 174.3, 164.93032)",
        "('Erbium', 157, 167.26)",
        "('Thulium', 172.4, 168.93421)",
        "('Ytterbium', 193, 173.04)",
        "('Lutetium', 171.8, 174.967)",
        "('Hafnium', 150, 178.49)",
        "('Tantal', 138, 180.9479)",
        "('Wolfram', 146, 183.84)",
        "('Rhenium', 159, 186.207)",
        "('Osmium', 128, 190.23)",
        "('Iridium', 137, 192.217)",
        "('Platin', 138, 195.084)",
        "('Gold', 144, 196.966569)",
        "('Quecksilber', 149, 200.59)",
        "('Thallium', 148, 204.3833)",
        "('Blei', 146, 207.2)",
        "('Bismut', 146, 208.98038)",
        "('Polonium', 153, 209.98)",
        "('Astat', 145, 209.9871)",
        "('Radon', 145, 222)",
        "('Francium', 260, 223.0197)",
        "('Radium', 221, 226.0254)",
        "('Actinium', 187.8, 227.0278)",
        "('Thorium', 165, 232.0381)",
        "('Protactinium', 163, 231.03588)",
        "('Uran', 142, 238.0289)",
        "('Neptunium', 155, 237.0482)",
        "('Plutonium', 187, 244.0642)",
        "('Americium', 228.5, 243.061375)",
        "('Curium', 174, 247.0703)",
        "('Berkelium', 170, 247)",
        "('Californium', 225, 251)",
        "('Einsteinium', 186, 252)",
        "('Fermium', 200, 2570951)"};
    // ### Atoms defined
    
    /**
     *
     * @throws java.sql.SQLException - Bedeutet das das erzeugen der initialen DB fehlgeschlagen ist
     * @throws java.lang.ClassNotFoundException - Bedeutet das der DB-Treiber nicht gefunden wurde
     * @throws java.lang.InstantiationException - Bedeutet das der DB-Treiber nicht Instanziert werden konnte
     * @throws java.lang.IllegalAccessException - ???
     */
    public dbConnection() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException
    {
        // Starten von Derby
        org.apache.derby.jdbc.EmbeddedSimpleDataSource ds = null;

        props.put("user", "admin");
        props.put("password", "admin");

        Class.forName( sDbDrv ).newInstance();
        // Verbindung aufbauen
        cn = DriverManager.getConnection(sDbUrl +";create=true", props);

        cn.setAutoCommit(false);

        // Statement erzeugen
        st = cn.createStatement();

        // Falls das Programm das erste mal gestartet wird...
        try
        {
            getDataSet("SELECT id FROM Atom");
        }
        catch (SQLException e)
        {
            // ... die Datenbank initialisieren
            createDB();
        }
    }

    String[] getDataSet(String sSql) throws SQLException { return getDataSet(sSql, null, 0); }
    String[] getDataSet(String sSql, String sOrder) throws SQLException { return getDataSet(sSql, sOrder, 0); }
    String[] getDataSet(String sSql, int iCount) throws SQLException { return getDataSet(sSql, null, iCount); }

    String[] getDataSet(String sSql, String sOrder, int iCount) throws SQLException
	{
		int n;
        // Falls alle Datens�tze ausgegeben werden sollen
        if (iCount<=0)
        {
            // SELECT-Anweisung mit Count konstruieren um die Anzahl der Datens�tze zu bekommen
            String sSqlTmp = "SELECT COUNT(*) "+ sSql.substring(sSql.indexOf("FROM"));
            // Datenbankanfrage abschicken
            rs = st.executeQuery( sSqlTmp );
            rs.next();
            iCount=Integer.parseInt(rs.getString(1));
        }

        if (sOrder!=null)
        {
            if (sSql.contains(";"))
                sSql = sSql.substring(0,sSql.length()-1) +" ORDER BY "+ sOrder;
            else
                sSql += " ORDER BY "+ sOrder;
        }
        // Datenbankanfrage abschicken
        rs = st.executeQuery( sSql );
        // Suchergebnis abspeichern
        ResultSetMetaData rsmd = rs.getMetaData();
        // Anzahl der Spalten abfragen
        n = rsmd.getColumnCount();
        // Initialisierung der R�ckgabearrays
        String [] sReturn = new String[iCount*n];

        int c=0;
        // �ber die Suchergebnisse, f�ge jedes Ergebnis in den Array ein
        while( rs.next() && c<iCount)
        {
            for (int i=1; i<=n; i++)
            {
                // speichere die einzelnen Werte der Abfrage in das R�ckgabe Array
                sReturn[(c*n)+i-1] = rs.getString(i);
            }
            c++;
        }
        return sReturn;
	}

    void createDB() throws SQLException
    {
         // Atom
        st.execute("CREATE TABLE Atom " +
            "(id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), " +
            "name VARCHAR(200), " +
            "radius FLOAT, " +
            "masse FLOAT)");
        // Atome in DB einfuegen
        for(final String atom : ATOMS)
            st.execute("INSERT INTO Atom(name, radius, masse) VALUES " + atom);
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Mendelevium', , 258)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Nobelium', , 259)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Lawrencium', , 260.1053)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Rutherfordium', , 261.1087)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Dubnium', , 262.1138)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Seaborgium', , 263.1182)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Bohrium', , 262.1229)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Hassium', , 265)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Meitnerium', , 268)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Darmstadtium', , 281)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Roentgenium', , 280)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Ununbium', , 277)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Ununtrium', , 287)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Ununquadium', , 289)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Ununpentium', , 288)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Ununhexium', , 289)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Ununseptium', , 291)");
        //st.execute("INSERT INTO Atom(name, radius, masse) VALUES ('Ununoctium', , 294)");
        
        cn.commit();
    }

    public boolean close()
    {
        // Verbindung, Statement und Datenbankanfrage zur�cksetzen
        try { if( null != rs ) rs.close(); } catch( Exception ex ) {}
        try { if( null != st ) st.close(); } catch( Exception ex ) {}
        try { if( null != cn ) cn.close(); } catch( Exception ex ) {}

        try
        {
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        }
        catch( SQLException ex)
        {
            // Das erfolgreiche schlie�en der Datenbank wirft einen speziellen "Fehler"
            return (ex.getErrorCode() == 45000);
        }
        return false;
    }

    public Atom[] getAlleAtome() throws SQLException{
        String tmp[] = getDataSet("SELECT * FROM Atom");
        Atom[] atom = new Atom[tmp.length/4];
        int count=0;
        for(int i=0; i<tmp.length; i+=4){
            atom[count++]=new Atom(Integer.parseInt(tmp[i]), tmp[i+1], Float.parseFloat(tmp[i+2]), Float.parseFloat(tmp[i+3]));
        }
        return atom;
    }

    
}